﻿Imports System.Data
Imports System.Data.SqlClient
Public Class DALAgendamento
    'Function LocalizaPessoa(ByVal codigo As Integer, ByVal descricao As String) As DataTable
    '    Dim cmd As SqlCommand
    '    Dim tab As DataTable
    '    Try
    '        cmd = New SqlCommand
    '        cmd.CommandText = "select agd_codigo,agd_nome from  vw_agendadores where " & _
    '            " pes_codigo= @codigo and agd_desc= @descricao"
    '        cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
    '        tab = objconAgenda.executaConsulta(cmd)
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        cmd = Nothing
    '    End Try
    '    Return tab
    'End Function
    Function excluir(ByVal age_codigo As Integer) As Integer
        Dim cmd As SqlCommand
        Dim total As Integer
        Try

            cmd = New SqlCommand
            cmd.CommandText = "delete from gen_agendamento where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = age_codigo
            total = objconAgenda.executaCmd(cmd)
        Catch ex As Exception
            Throw New Exception("", ex)
        Finally
            cmd = Nothing
        End Try
        Return total
    End Function
    Function LocalizaAgendamento() As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select age_codigo,age_data,age_horainicial,agd_nome,agd_desc,age_curso,sal_descricao from  vw_Agendamento "
            tab = objconAgenda.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaAgendamento(ByVal codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from  vw_Agendamento where age_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaRecurso(ByVal codigo As Integer, ByVal data As DateTime) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select rec_codigo,rec_descricao,rec_patrimonio from  vw_AgendamentoRecurso where age_data=@data and age_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.VarChar)).Value = data.ToShortDateString
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function inserir(ByVal data As String, ByVal usuario As Integer, ByVal sala As Integer, ByVal hinicio As DateTime, ByVal hfinal As DateTime, ByVal curso As String, ByVal codpes As Integer, ByVal tipo As String) As Integer
        Dim cmd As SqlCommand
        Dim codigo As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "insert into gen_agendamento(sal_codigo, age_data, agd_codigo,age_horainicial,age_horafinal,usu_codigo,age_curso,agd_desc,age_datacad)" & _
                "values(@sala,@data,@pessoa,@hinicio,@hfinal,@usuario,@curso,@tipo,@datacad)"
            cmd.Parameters.Add(New SqlParameter("@sala", SqlDbType.Int)).Value = sala
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.VarChar)).Value = data
            cmd.Parameters.Add(New SqlParameter("@pessoa", SqlDbType.Int)).Value = codpes
            cmd.Parameters.Add(New SqlParameter("@hinicio", SqlDbType.Time)).Value = hinicio.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@hfinal", SqlDbType.Time)).Value = hfinal.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@usuario", SqlDbType.Int)).Value = usuario
            cmd.Parameters.Add(New SqlParameter("@tipo", SqlDbType.VarChar)).Value = tipo
            cmd.Parameters.Add(New SqlParameter("@curso", SqlDbType.VarChar)).Value = curso
            cmd.Parameters.Add(New SqlParameter("@datacad", SqlDbType.VarChar)).Value = Date.Now
            codigo = objconAgenda.executaCmdAI(cmd, "gen_agendamento")
        Catch ex As Exception
            Throw New Exception("", ex)
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function

    Function alterar(ByVal data As String, ByVal usuario As Integer, ByVal sala As Integer, ByVal hinicio As DateTime, ByVal hfinal As DateTime, ByVal curso As String, ByVal codpes As Integer, ByVal tipo As String, ByVal age_codigo As Integer) As Integer
        Dim cmd As SqlCommand
        Dim ret As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = " update gen_agendamento set sal_codigo=@sala , age_data=@data, agd_codigo=@pessoa,age_horainicial=@hinicio,age_horafinal=@hfinal,usu_codigo=@usuario,age_curso=@curso,agd_desc=@tipo,age_datacad=@datacad where age_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@sala", SqlDbType.Int)).Value = sala
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.VarChar)).Value = data
            cmd.Parameters.Add(New SqlParameter("@pessoa", SqlDbType.Int)).Value = codpes
            cmd.Parameters.Add(New SqlParameter("@hinicio", SqlDbType.Time)).Value = hinicio.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@hfinal", SqlDbType.Time)).Value = hfinal.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@usuario", SqlDbType.Int)).Value = usuario
            cmd.Parameters.Add(New SqlParameter("@tipo", SqlDbType.VarChar)).Value = tipo
            cmd.Parameters.Add(New SqlParameter("@curso", SqlDbType.VarChar)).Value = curso
            cmd.Parameters.Add(New SqlParameter("@datacad", SqlDbType.VarChar)).Value = Date.Now
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = age_codigo
            objconAgenda.executaCmd(cmd)
        Catch ex As Exception

            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function


#Region "Professores"
    Function LocalizaProfessor(ByVal Nome As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,pro_matricula from  vw_LocalizaAgendadores where " & _
                     " agd_nome like @nome and agd_desc='Professor'"
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = "%" & Nome & "%"

            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaProfessorMatricula(ByVal Matricula As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,pro_matricula from  vw_LocalizaAgendadores where " & _
                     " pro_matricula = @mat and agd_desc='Professor'"
            cmd.Parameters.Add(New SqlParameter("@mat", SqlDbType.VarChar)).Value = Matricula

            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaProfessor(ByVal codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,pro_matricula from  vw_LocalizaAgendadores where " & _
                     " agd_codigo = @cod and agd_desc= 'Professor'"
            cmd.Parameters.Add(New SqlParameter("@cod", SqlDbType.Int)).Value = codigo

            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaPessoa(ByVal nome As String, ByVal descricao As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,alu_ra,fun_matricula,pro_matricula from  vw_LocalizaAgendadores where " & _
                     " agd_nome like @nome and agd_desc= @descricao order by agd_desc"
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = "%" & nome & "%"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaPessoa(ByVal codigo As Integer, ByVal descricao As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,alu_ra,fun_matricula,pro_matricula from  vw_LocalizaAgendadores where " & _
                     " agd_codigo = @cod and agd_desc= @descricao"
            cmd.Parameters.Add(New SqlParameter("@cod", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Aluno"
    Function LocalizaAluno(ByVal codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,alu_ra from  vw_LocalizaAgendadores where " & _
                     " agd_codigo = @cod and agd_desc= 'Aluno'"
            cmd.Parameters.Add(New SqlParameter("@cod", SqlDbType.Int)).Value = codigo
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaAluno(ByVal Nome As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,alu_ra from  vw_LocalizaAgendadores where " & _
                     " agd_nome Like @Nome and agd_desc= 'Aluno'"
            cmd.Parameters.Add(New SqlParameter("@Nome", SqlDbType.VarChar)).Value = "%" & Nome & "%"
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaAlunoRA(ByVal RA As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,alu_ra from  vw_LocalizaAgendadores where " & _
                     " alu_ra = @ra and agd_desc= 'Aluno'"
            cmd.Parameters.Add(New SqlParameter("@ra", SqlDbType.VarChar)).Value = RA
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Funcionários"
    Function LocalizaFuncionario(ByVal codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,fun_matricula from  vw_LocalizaAgendadores where " & _
                     " agd_codigo = @cod and agd_desc= 'Funcionário'"
            cmd.Parameters.Add(New SqlParameter("@cod", SqlDbType.Int)).Value = codigo
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaFuncionario(ByVal Nome As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,fun_matricula from  vw_LocalizaAgendadores where " & _
                     " agd_nome Like @Nome and agd_desc= 'Funcionário'"
            cmd.Parameters.Add(New SqlParameter("@Nome", SqlDbType.VarChar)).Value = "%" & Nome & "%"
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaFuncionarioMatricula(ByVal Matricula As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select agd_codigo,agd_nome,fun_matricula from  vw_LocalizaAgendadores where " & _
                     " fun_matricula = @mat and agd_desc= 'Funcionário'"
            cmd.Parameters.Add(New SqlParameter("@mat", SqlDbType.VarChar)).Value = Matricula
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Sala"
    Function LocalizaSala(Filtro As Integer, ByVal Descricao As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case Filtro
                Case 0
                    cmd.CommandText = "select *from gen_sala where sal_bloco like @desc"
                    cmd.Parameters.Add(New SqlParameter("@desc", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 1
                    cmd.CommandText = "select *from gen_sala where sal_codigo = @cod"
                    cmd.Parameters.Add(New SqlParameter("@cod", SqlDbType.Int)).Value = Descricao
                Case 2
                    cmd.CommandText = "select *from gen_sala where sal_descricao like @desc"
                    cmd.Parameters.Add(New SqlParameter("@desc", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 3
                    cmd.CommandText = "select *from gen_sala where sal_sala like @desc"
                    cmd.Parameters.Add(New SqlParameter("@desc", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
            End Select

            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaSala(ByVal codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select *from gen_sala where sal_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.VarChar)).Value = codigo
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region

    Function LocalizaRecurso(ByVal nome As String) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select rec_codigo,rec_descricao,rec_patrimonio from vw_Recurso where rec_descricao like @nome and rec_status='Ativado' and rec_estado='A'"
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = "%" & nome & "%"
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function LocalizaRecurso(ByVal codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select rec_codigo,rec_descricao,rec_patrimonio from vw_Recurso where rec_codigo = @codigo and rec_status='Ativado' and rec_estado='A'"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function

    Function localiza(ByVal agecod As Integer) As DataTable
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from age_agendamento where age_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = agecod
            tab = objconAgenda.retdadosTab(cmd)
        Catch ex As Exception
            Throw New Exception("", ex)
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function ValidaSala(ByVal diasemana As String, ByVal horainicio As DateTime, ByVal horafim As DateTime, ByVal codsala As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM gen_particulariedades WHERE sal_codigo = @codsala and par_dia = @diasemana and " & _
                                "((par_horainicio BETWEEN @horainicio AND @horafim) OR (par_horafinal  BETWEEN @horainicio AND @horafim))"
            cmd.Parameters.Add(New SqlParameter("@diasemana", SqlDbType.VarChar)).Value = diasemana
            cmd.Parameters.Add(New SqlParameter("@horainicio", SqlDbType.Time)).Value = horainicio.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@horafim", SqlDbType.Time)).Value = horafim.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@codsala", SqlDbType.Int)).Value = codsala
            tab = objconAgenda.executaConsulta(cmd)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function ValidaRecurso(ByVal data As String, ByVal codrecurso As Integer, ByVal horainicio As DateTime, ByVal horafim As DateTime) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Dim ret As Boolean = True
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM vw_AgendamentoRecurso WHERE age_data = @data and rec_codigo = @reccod and " & _
                                "((age_horainicial BETWEEN @horainicio AND @horafim) OR (age_horafinal  BETWEEN @horainicio AND @horafim))"
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.VarChar)).Value = data
            cmd.Parameters.Add(New SqlParameter("@horainicio", SqlDbType.Time)).Value = horainicio.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@horafim", SqlDbType.Time)).Value = horafim.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@reccod", SqlDbType.Int)).Value = codrecurso
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function ValidaInsere(ByVal data As String, ByVal codsala As Integer, ByVal horainicio As DateTime, ByVal horafim As DateTime) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Dim ret As Boolean = True
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM vw_Agendamento WHERE age_data = @data and sal_codigo = @salcod and " & _
                                "((age_horainicial BETWEEN @horainicio AND @horafim) OR (age_horafinal  BETWEEN @horainicio AND @horafim))"
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.VarChar)).Value = data
            cmd.Parameters.Add(New SqlParameter("@horainicio", SqlDbType.Time)).Value = horainicio.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@horafim", SqlDbType.Time)).Value = horafim.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@salcod", SqlDbType.Int)).Value = codsala
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    Function ValidaAlterar(ByVal data As String, ByVal codsala As Integer, ByVal horainicio As DateTime, ByVal horafim As DateTime, ByVal age_codigo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Dim ret As Boolean = True
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM vw_Agendamento WHERE age_data = @data and sal_codigo = @salcod and age_codigo <> @agecodigo and " & _
                                "((age_horainicial BETWEEN @horainicio AND @horafim) OR (age_horafinal  BETWEEN @horainicio AND @horafim))"
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.VarChar)).Value = data
            cmd.Parameters.Add(New SqlParameter("@horainicio", SqlDbType.Time)).Value = horainicio.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@horafim", SqlDbType.Time)).Value = horafim.ToShortTimeString
            cmd.Parameters.Add(New SqlParameter("@salcod", SqlDbType.Int)).Value = codsala
            cmd.Parameters.Add(New SqlParameter("@agecodigo", SqlDbType.Int)).Value = age_codigo
            tab = objconAgenda.executaConsulta(cmd)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
End Class
